package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Price;
import com.wanmait.hotelManage.vo.RoomType;

public class RoomTypeDAO {
	private RoomTypeDAO() {
	};

	private static RoomTypeDAO roomtypedao = new RoomTypeDAO();

	public static RoomTypeDAO getRoomtypedao() {
		return roomtypedao;
	}

	public RoomType roomTypeDetalis(String roomTypeName) {
		StringBuilder sb=new StringBuilder();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		RoomType roomType=null;
		String sql=" SELECT * FROM room_type  WHERE title=? ";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, roomTypeName);
			rs=ps.executeQuery();
			if(rs.next()) {
				
				roomType=new RoomType();
				roomType.setDescription(rs.getString("description"));
				roomType.setDiscountPrice(rs.getDouble("discount_price"));
				roomType.setEnable(rs.getBoolean("enable"));
				roomType.setHot(rs.getBoolean("hot"));
				roomType.setId(rs.getInt("id"));
				roomType.setInputTime(rs.getTimestamp("input_time"));
				roomType.setPrice(rs.getDouble("price"));
				roomType.setTitle(rs.getString("title"));
				roomType.setPic(rs.getString("pic"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return roomType;
	}

	/**
	 * 查找所有
	 * @return
	 */
	public List<RoomType> findAll() {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RoomType> roomTypes = new ArrayList<RoomType>();
		RoomType roomType = null;
//		Roomimages roomimages=null;
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT id,title,description,price,discount_price, ");
		sb.append(" pic,hot,ENABLE,input_time FROM room_type ");
		sb.append(" WHERE ENABLE=1  ");
		String sql = sb.toString();

		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				roomType = new RoomType();
				roomType.setDescription(rs.getString("description"));
				roomType.setDiscountPrice(rs.getDouble("discount_price"));
				roomType.setEnable(rs.getBoolean("enable"));
				roomType.setHot(rs.getBoolean("hot"));
				roomType.setId(rs.getInt("room_type.id"));
				roomType.setInputTime(rs.getTimestamp("input_time"));
				roomType.setPrice(rs.getDouble("price"));
				roomType.setTitle(rs.getString("title"));


//				roomimages =new Roomimages();
//				roomimages.setId(rs.getInt("room_imges.id"));

				

				roomType.setPic(rs.getString("pic"));
				roomTypes.add(roomType);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return roomTypes;
	}



	//分页查找全部房型
	public List<RoomType> findAll(Pager pager) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<RoomType> roomTypes=new ArrayList<RoomType>();
		RoomType roomType=null;

		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT id,title,description,price,discount_price, ");
		sb.append(" pic,hot,ENABLE,input_time FROM room_type ");
		sb.append(" WHERE ENABLE=1 limit ?,?  ");
		String sql=sb.toString();
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				roomType=new RoomType();
				roomType.setDescription(rs.getString("description"));
				roomType.setDiscountPrice(rs.getDouble("discount_price"));
				roomType.setEnable(rs.getBoolean("enable"));
				roomType.setHot(rs.getBoolean("hot"));
				roomType.setId(rs.getInt("room_type.id"));
				roomType.setInputTime(rs.getTimestamp("input_time"));
				roomType.setPrice(rs.getDouble("price"));
				roomType.setTitle(rs.getString("title"));
				
				roomType.setPic(rs.getString("pic"));
				roomTypes.add(roomType);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return roomTypes;
	}
	/**
	 * 鏍规嵁id鍒犻櫎roomtype
	 * 
	 * @param id
	 */
	public void delete(int id) {
		// TODO Auto-generated method stub
		String sql = "update room_type set enable=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}

	/**
	 * 鏍规嵁ids鎵归噺鍒犻櫎
	 * 
	 * @param ids
	 */
	public void delete(String ids) {
		// TODO Auto-generated method stub
		String sql = "update room_type set enable=0 where id in(" + ids + ")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}

	/**
	 * 鏍规嵁id鏌ユ壘鎴垮瀷
	 * 
	 * @param id
	 * @return
	 */
	public int getRows() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) AS c FROM room_type WHERE ENABLE=1 ";
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			int rows = rs.getInt("c");
			
			return rows;
		} catch (Exception e) {
			
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return 0;
	}
	public List<RoomType> getPageAllRoomType(Pager pager){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<RoomType> roomTypes=new ArrayList(); 
		StringBuilder sb=new StringBuilder();
		
	String	sql=" SELECT * FROM room_type  WHERE ENABLE=1 LIMIT ?,? ";
	try {
		conn=DBHelper.getDBHelper().getConn();
		ps=conn.prepareStatement(sql);
		ps.setInt(1, pager.getStart());
		ps.setInt(2,pager.getPageSize());
		rs=ps.executeQuery();
		while(rs.next()) {
			RoomType roomtype=new RoomType();
			roomtype.setDescription(rs.getString("description"));
			roomtype.setDiscountPrice(rs.getDouble("discount_price"));
			roomtype.setEnable(rs.getBoolean("enable"));
			roomtype.setHot(rs.getBoolean("hot"));
			roomtype.setId(rs.getInt("id"));
			roomtype.setInputTime(rs.getTimestamp("input_time"));
			roomtype.setPic(rs.getString("pic"));
			roomtype.setPrice(rs.getDouble("price"));
			roomtype.setTitle(rs.getString("title"));
			
			roomTypes.add(roomtype);
		}
	} catch (Exception e) {
		// TODO: handle exception
	}
	finally {
		DBHelper.getDBHelper().close(rs, ps, conn);
	}
	return roomTypes;
	}
 
	public List<RoomType> hotRoom() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		List<RoomType> roomTypes = new ArrayList();
		sb.append(" select id,title,description,price ").append(",discount_price,pic,hot ")
				.append(" ,ENABLE,input_time FROM ").append(" room_type WHERE hot=1 ");
		String sql = sb.toString();

		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				RoomType roomType = new RoomType();
				roomType.setDescription(rs.getString("description"));
				roomType.setDiscountPrice(rs.getDouble("discount_price"));
				roomType.setEnable(rs.getBoolean("enable"));
				roomType.setHot(rs.getBoolean("hot"));
				roomType.setId(rs.getInt("id"));
				roomType.setInputTime(rs.getTimestamp("input_time"));
				roomType.setPic(rs.getString("pic"));
				roomType.setPrice(rs.getDouble("price"));
				roomType.setTitle(rs.getString("title"));
				roomTypes.add(roomType);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return roomTypes;
	}
	//根据房型id查找房型
	/**
	 * 根据id找对象
	 * @param id
	 * @return
	 */
	public RoomType findRoomTypeById(Integer id) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		RoomType roomType = null;
//		Roomimages roomimages=null;

		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT id,title,description,price,discount_price, ");
		sb.append(" hot,ENABLE,input_time,pic FROM room_type ");
		sb.append(" WHERE ENABLE=1 and room_type.id=? ");
		String sql = sb.toString();

		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();

			if (rs.next()) {
				roomType = new RoomType();
//				roomimages =new Roomimages();
//				
//				roomimages.setId(rs.getInt("room_imges.id"));
//				roomimages.setPic(rs.getString("pic"));

				roomType.setPic(rs.getString("pic"));
				roomType.setDescription(rs.getString("description"));
				roomType.setDiscountPrice(rs.getDouble("discount_price"));
				roomType.setEnable(rs.getBoolean("enable"));
				roomType.setHot(rs.getBoolean("hot"));
				roomType.setId(rs.getInt("room_type.id"));
				roomType.setInputTime(rs.getTimestamp("input_time"));
				roomType.setPrice(rs.getDouble("price"));
				roomType.setTitle(rs.getString("title"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return roomType;
	}

	/**
	 * 鏇存柊鍖呭惈鍥剧墖roomtype
	 * 
	 * @param roomType
	 */
	public void update(RoomType roomType) {
		// TODO Auto-generated method stub
		StringBuilder sb=new StringBuilder();
		sb.append(" update room_type set title=?,description=?,price=?,discount_price=?,pic=?,hot=? ");

		sb.append(" where id=? ");
		
		DBHelper.getDBHelper().executeUpdate(
				sb.toString(),
				roomType.getTitle(),
				roomType.getDescription(),
				roomType.getPrice(),
				roomType.getDiscountPrice(),
				roomType.getPic(),
				roomType.getHot(),
				roomType.getId()
				);	

	}

	/**
	 * 鏇存柊涓嶅寘鍚浘鐗�
	 * 
	 * @param roomType
	 */
	public void noImageUpdate(RoomType roomType) {
		// TODO Auto-generated method stub

		StringBuilder sb = new StringBuilder();
		sb.append(" update room_type set title=?,description=?,price=?,discount_price=?,");
		sb.append(" hot=? where id=?");
		DBHelper.getDBHelper().executeUpdate(sb.toString(), 
				roomType.getTitle(), 
				roomType.getDescription(),
				roomType.getPrice(), 
				roomType.getDiscountPrice(), 
				roomType.getHot(),
				roomType.getId());
	}

	public void insert(RoomType roomtype) {
		
		String sql="insert into room_type(title,price,discount_price,description,pic) values(?,?,?,?,?)";
		Object[]params= {
			roomtype.getTitle(),
			roomtype.getPrice(),
			roomtype.getDiscountPrice(),
			roomtype.getDescription(),
			roomtype.getPic()
		};
		DBHelper.getDBHelper().executeUpdate(sql, params);
	}

	public RoomType findTitle(String title) {
		RoomType roomtype = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from room_type where title=?";
		conn = DBHelper.getDBHelper().getConn();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, title);
			rs = ps.executeQuery();
			if (rs.next()) {
				roomtype = new RoomType();
				roomtype.setId(rs.getInt("id"));
				roomtype.setTitle(title);
				roomtype.setPrice(rs.getDouble("price"));
				roomtype.setDiscountPrice(rs.getDouble("discount_price"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return roomtype;
	}

	public void updatePrice(RoomType roomtype) {
		String sql = "update room_type set title=?,price=?,discount_price=? where id=?";
		Object[] params = { roomtype.getTitle(), roomtype.getPrice(), roomtype.getDiscountPrice(), roomtype.getId() };
		DBHelper.getDBHelper().executeUpdate(sql, params);
	}

	public void updatePrice1(String title, Double price, Double discountPrice, Integer id) {
		String sql = "update room_type set title=?,price=?,discount_price=? where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, title, price, discountPrice, id);
	}
	/**
	 * 查找价钱
	 * @param id
	 * @return
	 */
	public RoomType findprice(Integer id) {
		RoomType roomtype=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from room_type where id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				roomtype=new RoomType();
				roomtype.setId(id);
				roomtype.setPic(rs.getString("pic"));
				roomtype.setDescription(rs.getString("description"));
				roomtype.setDiscountPrice(rs.getDouble("discount_price"));
				roomtype.setEnable(rs.getBoolean("enable"));
				roomtype.setHot(rs.getBoolean("hot"));
				roomtype.setInputTime(rs.getTimestamp("input_time"));
				roomtype.setPrice(rs.getDouble("price"));
				roomtype.setTitle(rs.getString("title"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return roomtype;
	}
	


	public void insertTitle(String title, Double price, Double discountPrice) {
		String sql = "insert into room_type(title,price,discount_price) values(" + title + "," + price + ","
				+ discountPrice + ")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	public Integer findRoomTypeId() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		int roomTypeId=0;
		String sql="select max(id) id from room_type ";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			roomTypeId=rs.getInt("id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return roomTypeId;
	}
	//首页搜索,根据房型,入住,结束时间
	public Boolean search(String roomType,  List<Timestamp> timestamps) {
		// TODO Auto-generated method stub
		List<RoomType> roomTypes=new ArrayList<RoomType>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		String sql=" SELECT SUM(c)<(" + 
				"	SELECT COUNT(*)" + 
				"	FROM room,room_type" + 
				"	WHERE room_type_id=room_type.id" +
				"   and room_type.title=? "+
				/*if(price!=null) {
					sql+=" and discount_price between  "+price.getMin()+" and "+price.getMax();
				}*/
				"   ) flag  " + 
				
				"   FROM(" + 
				"	SELECT COUNT(*) c" + 
				"	FROM orders" + 
				"	WHERE date(?) BETWEEN date(start_time) " + 
				"	AND SUBDATE(date(end_time),INTERVAL 1 DAY)" + 
				"	AND room_type_id=(select id from room_type where title=?)"+
				"   and enable=1  and reg=0 "+
				"  "+
				"	UNION ALL " + 
				"	SELECT COUNT(DISTINCT room_id) c" + 
				"	FROM register" + 
				"	WHERE ENABLE=1" + 
				"	AND date(?) BETWEEN DATE(NOW()) " + 
				"	AND date_sub(DATE(end_time),INTERVAL 1 DAY) " + 
				"	AND room_id IN(SELECT room.id FROM room,room_type" + 
				"	WHERE room.room_type_id=room_type.id" + 
				"	AND room_type.title=?" + 
				"	)"+
				"   ) a";
		
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			for(Timestamp timestamp:timestamps) {
//				System.out.println(roomType);
//				System.out.println(timestamp);
				ps=conn.prepareStatement(sql);
				ps.setString(1, roomType);
				ps.setString(2, timestamp.toString());
				ps.setString(3, roomType);
				ps.setString(4, timestamp.toString());
				ps.setString(5, roomType);
				rs=ps.executeQuery();
				if(rs.next()) {
					System.out.println(rs.getBoolean("flag"));
//					System.out.println(rs.getInt("cc"));
					if(!rs.getBoolean("flag")) {
						return false;
					}
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return true;
	}
	//无房型条件搜索
	public List<RoomType> search(List<RoomType> roomTypes, List<Timestamp> timestamps, Pager pager) {
		// TODO Auto-generated method stub
		List<RoomType> searchRoomTypes=new ArrayList<RoomType>();
		
		String sql=" SELECT * " + 
				" FROM( " ; 
				
			for(int i=0;i<roomTypes.size();i++) {
				if(i!=0) {
					sql+=" union all ";
				}
				sql+="	SELECT COUNT(*)=0 f ,'"+roomTypes.get(i).getTitle()+"'" + 
						" FROM( " ;
					for(int j=0;j<timestamps.size();j++) {
						if(j!=0) {
							sql+=" union all ";
						}
						sql+=	" SELECT SUM(c)<( " + 
								" SELECT COUNT(*) " + 
								" FROM room,room_type " + 
								" WHERE room_type_id=room_type.id " + 
								" AND room_type.title='"+roomTypes.get(i).getTitle()+"' " + 
								" ) flag,'"+roomTypes.get(i).getTitle()+"' " + 
								" FROM( " + 
								" SELECT COUNT(*) c " + 
								" FROM orders " + 
								" WHERE '"+timestamps.get(j)+"' BETWEEN date(start_time)  " + 
								" AND SUBDATE(date(end_time),INTERVAL 1 DAY) " + 
								" AND room_type_id="+roomTypes.get(i).getId()+" " + 
								" and enable=1 and reg=0   "+
								" UNION ALL  " + 
								" SELECT COUNT(DISTINCT room_id) " + 
								" FROM register " + 
								" WHERE ENABLE=1 " + 
								" AND '"+timestamps.get(j)+"' BETWEEN DATE(NOW())  " + 
								" AND date_sub(DATE(end_time),INTERVAL 1 DAY)  " + 
								" AND room_id IN(SELECT room.id FROM room,room_type " + 
								" WHERE room.room_type_id=room_type.id " + 
								" AND room_type.title='"+roomTypes.get(i).getTitle()+"'" + 
								" ) " + 
								" ) a " ;
					}
					sql+=" ) w  ";
					sql+=" WHERE flag=0 ";
			}
				sql+=") b,room_type " + 
						" WHERE f=1 "
						+ " and "+roomTypes.get(0).getTitle()+"=room_type.title "
						+ " limit ?,? ";
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				RoomType roomtype=new RoomType();
				roomtype.setDescription(rs.getString("description"));
				roomtype.setDiscountPrice(rs.getDouble("discount_price"));
				roomtype.setEnable(rs.getBoolean("enable"));
				roomtype.setHot(rs.getBoolean("hot"));
				roomtype.setId(rs.getInt("id"));
				roomtype.setInputTime(rs.getTimestamp("input_time"));
				roomtype.setPic(rs.getString("pic"));
				roomtype.setPrice(rs.getDouble("price"));
				roomtype.setTitle(rs.getString("title"));
				
				searchRoomTypes.add(roomtype);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return searchRoomTypes;
	}
	//获得搜索后的行数
	public int getSearchRows(List<RoomType> roomTypes, List<Timestamp> timestamps) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

				String sql="SELECT COUNT(*) count " + 
						"FROM( " ; 
						
					for(int i=0;i<roomTypes.size();i++) {
						if(i!=0) {
							sql+=" union all ";
						}
						sql+="	SELECT COUNT(*)=0 f " + 
								"FROM( " ;
							for(int j=0;j<timestamps.size();j++) {
								if(j!=0) {
									sql+=" union all ";
								}
								sql+=	" SELECT SUM(c)<( " + 
										" SELECT COUNT(*) " + 
										" FROM room,room_type " + 
										" WHERE room_type_id=room_type.id " + 
										" AND room_type.title='"+roomTypes.get(i).getTitle()+"' " + 
										" ) flag,'"+roomTypes.get(i).getTitle()+"' " + 
										" FROM( " + 
										" SELECT COUNT(*) c " + 
										" FROM orders " + 
										" WHERE '"+timestamps.get(j)+"' BETWEEN date(start_time)  " + 
										" AND SUBDATE(date(end_time),INTERVAL 1 DAY) " + 
										" AND room_type_id="+roomTypes.get(i).getId()+" " + 
										" and enable=1 and reg=0 "+
										" UNION ALL  " + 
										" SELECT COUNT(DISTINCT room_id) " + 
										" FROM register " + 
										" WHERE ENABLE=1 " + 
										" AND '"+timestamps.get(j)+"' BETWEEN DATE(NOW())  " + 
										" AND date_sub(DATE(end_time),INTERVAL 1 DAY)  " + 
										" AND room_id IN(SELECT room.id FROM room,room_type " + 
										" WHERE room.room_type_id=room_type.id " + 
										" AND room_type.title='"+roomTypes.get(i).getTitle()+"'" + 
										" ) " + 
										" ) a " ;
							}
							sql+=" ) w  ";
							sql+=" WHERE flag=0 ";
					}
						sql+=") b " + 
								"WHERE f=1";
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			int rows = rs.getInt("count");
			return rows;
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return 0;
	}

	public Double getPriceByRoomTypeTitle(String roomType) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select discount_price d from room_type where title=?";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, roomType);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getDouble("d");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return null;
	}

	}
